package cn.stazxr;

import org.junit.Test;

import java.sql.Connection;
import java.sql.Date;
import java.sql.Driver;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Properties;

/**
 * @author talentestors
 * @version 1.0
 * @since 2025.05.19
 **/
@SuppressWarnings("ALL")
public class JdbcTest {

	@Test
	public void test1() throws Exception {
		//1.数据库连接的4个基本要素：
		String url = "jdbc:mysql://192.168.182.128:3306/test?useUnicode=true&characterEncoding=utf8&useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=Asia/Shanghai";
		String user = "test";
		String password = "root";
		//8.0之后名字改了  com.mysql.cj.jdbc.Driver
		//8.0之前  com.mysql.jdbc.Driver
		String driverName = "com.mysql.cj.jdbc.Driver";

		//2.实例化Driver
		Class<?> clazz = Class.forName(driverName);
		Driver driver = (Driver) clazz.getDeclaredConstructor().newInstance();
		//3.注册驱动
		DriverManager.registerDriver(driver);
		//4.获取连接
		Connection conn = DriverManager.getConnection(url, user, password);
		System.out.println(conn);
	}

	@Test
	public void test2() throws Exception {
		//1.数据库连接的4个基本要素：
		String url = "jdbc:mysql://192.168.182.128:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
		String user = "test";
		String password = "root";
		//8.0之后名字改了  com.mysql.cj.jdbc.Driver
		//8.0之前  com.mysql.jdbc.Driver
		String driverName = "com.mysql.cj.jdbc.Driver";

		//2.实例化Driver
		//3.注册驱动
		Class.forName(driverName);
		//4.获取连接
		Connection conn = DriverManager.getConnection(url, user, password);
		System.out.println(conn);
	}

	@Test
	public void test3() throws Exception {
		//1.数据库连接的4个基本要素：
		String url = "jdbc:mysql://192.168.182.128:3306/test?useUnicode=true&characterEncoding=utf8&serverTimezone=Asia/Shanghai";
		String user = "test";
		String password = "root";
		//8.0之后名字改了  com.mysql.cj.jdbc.Driver
		//8.0之前  com.mysql.jdbc.Driver

		//2.实例化Driver
		//3.注册驱动
		/* Java提供SPI机制，自动加载驱动，可以不手动注册.
		 * MySQL 8.0, 使用了SPI机制 */
		//4.获取连接
		Connection conn = DriverManager.getConnection(url, user, password);
		System.out.println(conn);
	}

	@Test
	public void test4() throws Exception {
		//1.数据库连接的4个基本要素：
		Properties properties = new Properties();
		properties.load(JdbcTest.class.getClassLoader().getResourceAsStream("jdbc.properties"));
		String url = properties.getProperty("mysql.url");
		String user = properties.getProperty("mysql.username");
		String password = properties.getProperty("mysql.password");
		//8.0之后名字改了  com.mysql.cj.jdbc.Driver
		//8.0之前  com.mysql.jdbc.Driver

		//2.实例化Driver
		//3.注册驱动
		/* Java提供SPI机制，自动加载驱动，可以不手动注册.
		 * MySQL 8.0, 使用了SPI机制 */
		//4.获取连接
		Connection conn = DriverManager.getConnection(url, user, password);
		System.out.println(conn);
	}

	@Test
	public void testStatement1() {
		Connection connection = null;
		Statement statement = null;
		try {
			connection = JdbcUtil.getConnection();
			statement = connection.createStatement();
			int i = statement.executeUpdate("insert into user values (2, 'zhangsan', 'zhangsan','2001-01-01'),(3, 'zhangsan', 'zhangsan','2001-01-01')");
			System.out.println(i);
		} catch (SQLException e) {
			new RuntimeException(e);
		} finally {
			if (statement != null) {
				try {
					statement.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
			if (connection != null) {
				try {
					connection.close();
				} catch (SQLException e) {
					throw new RuntimeException(e);
				}
			}
		}
	}

	@Test
	public void testStatement2() throws SQLException {
		String sql = "select * from user";
		Connection connection = JdbcUtil.getConnection();
		Statement statement = connection.createStatement();
		ResultSet resultSet = statement.executeQuery(sql);
		while (resultSet.next()) {
			int id = resultSet.getInt("id");
			String name = resultSet.getString("username");
			String password = resultSet.getString("password");
			Date birthday = resultSet.getDate("birthday");
			System.out.println(id + " " + name + " " + password + " " + birthday);
		}
		System.out.println(resultSet.next());
		JdbcUtil.closeAll(connection, statement, resultSet);
	}

	@Test
	public void testStatement3() {
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = JdbcUtil.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select * from user");
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("username");
				String password = resultSet.getString("password");
				Date birthday = resultSet.getDate("birthday");
				System.out.println(id + " " + name + " " + password + " " + birthday);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.closeAll(connection, statement, resultSet);
		}
	}

	public static boolean login(String username, String password) {
		String sql = "select * from user where username ='" + username + "' and password = '" + password + "'";
		Connection connection = null;
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = JdbcUtil.getConnection();
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			return resultSet.next();
		} catch (SQLException e) {
			e.printStackTrace();
			return false;
		} finally {
			JdbcUtil.closeAll(connection, statement, resultSet);
		}
	}

	public static void main(String[] args) {
		// false
		System.out.println("login(\"lisi\", \"123\") = " + login("lisi", "123456"));
		// true
		System.out.println("login(\"lisi\", \"lisi\") = " + login("lisi", "lisi"));
		// true SQL注入
		boolean lisi = login("lisi", "123' or '1'='1");
		System.out.println("SQL injection: " + lisi);
	}

	@Test
	public void testPreparedStatement1() {
		Connection connection = null;
		PreparedStatement statement = null;
		String sql = "update user set username = 'wangwu' where id = 1";
		try {
			connection = JdbcUtil.getConnection();
			statement = connection.prepareStatement(sql);
			statement.execute();
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.closeAll(connection, statement, null);
		}
	}

	@Test
	public void testPreparedStatement2() {
		Connection connection = null;
		PreparedStatement statement = null;
		String sql = "update user set username = ? where id = ?";
		try {
			connection = JdbcUtil.getConnection();
			statement = connection.prepareStatement(sql);
			statement.setString(1, "zhanliu2");
			statement.setInt(2, 2);
			int i = statement.executeUpdate();
			System.out.println(i);
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.closeAll(connection, statement, null);
		}
	}

	@Test
	public void testPreparedStatement3() {
		Connection connection = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		String sql = "select * from user where id > ?";
		try {
			connection = JdbcUtil.getConnection();
			statement = connection.prepareStatement(sql);
			statement.setInt(1, 1);
			resultSet = statement.executeQuery();
			while (resultSet.next()) {
				int id = resultSet.getInt("id");
				String name = resultSet.getString("username");
				String password = resultSet.getString("password");
				Date birthday = resultSet.getDate("birthday");
				System.out.println(id + " " + name + " " + password + " " + birthday);
			}
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.closeAll(connection, statement, resultSet);
		}
	}

	public static int updateForTest5(Connection connection, PreparedStatement statement, int id, String name) {
		try {
			statement.setString(1, name);
			statement.setInt(2, id);
			int i = statement.executeUpdate();
			// 提交事务
			connection.commit();
			return i;
		} catch (Exception e) {
			// 回滚事务
			try {
				if (connection != null) {
					connection.rollback();
				}
			} catch (SQLException ex) {
				throw new RuntimeException(ex);
			}
			e.printStackTrace();
			return 0;
		}

	}

	public static int updateForTest5Exception(Connection connection, PreparedStatement statement, int id, String name) {
		try {
			statement.setString(1, name);
			statement.setInt(2, id);
			int i = statement.executeUpdate();
			// 模拟异常
			int res = i / 0;
			// 提交事务
			connection.commit();
			return i;
		} catch (Exception e) {
			// 回滚事务
			try {
				if (connection != null) {
					connection.rollback();
				}
			} catch (SQLException ex) {
				throw new RuntimeException(ex);
			}
			e.printStackTrace();
			return 0;
		}
	}

	@Test
	public void test5() {
		String sql = "update user set username = ? where id = ?";
		Connection connection = JdbcUtil.getConnection();
		PreparedStatement statement = null;
		try {
			// 取消自动提交事务
			connection.setAutoCommit(false);
			statement = connection.prepareStatement(sql);
			System.out.println(updateForTest5(connection, statement, 1, "test1"));
			System.out.println(updateForTest5(connection, statement, 2, "test2"));
			System.out.println(updateForTest5(connection, statement, 0, "test"));
			// 模拟异常
			System.out.println(updateForTest5Exception(connection, statement, 1, "test"));
		} catch (SQLException e) {
			throw new RuntimeException(e);
		} finally {
			JdbcUtil.closeAll(connection, statement, null);
		}
	}

}
